Writing JPQL and Native SQL Queries

Course: Advance Java Training

Topic: Writing JPQL and Native SQL Queries Using Spring Data JPA

Course Link: Advance Java Training Course

Demo Link: Register for a Free Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to JPQL and Native SQL Queries

In Java applications, developers frequently need to retrieve, filter, update, and delete records stored in a relational database. Spring Data JPA simplifies database access by providing repository interfaces, derived query methods, and custom query definitions.

When a query requires more control than a standard repository method can provide, developers can write Java Persistence Query Language (JPQL) queries or native SQL queries. JPQL works with entities and their Java properties, whereas native SQL works directly with database tables and columns.

Spring Data JPA supports manually declared queries through the @Query annotation. Native SQL can be enabled with nativeQuery = true. In supported recent Spring Data JPA versions, the @NativeQuery annotation is also available.

2. Learning Objectives

• Understand the purpose and differences between JPQL and native SQL.
• Write JPQL queries using entity names, aliases, and entity properties.
• Use SELECT, WHERE, ORDER BY, GROUP BY, and aggregate functions.
• Implement named and positional query parameters.
• Create native SQL queries using Spring Data JPA.
• Perform update and delete operations with custom queries.
• Understand joins, pagination, sorting, and query result handling.
• Apply query security, performance optimization, and troubleshooting practices.

3. What Is JPQL?

JPQL (Java Persistence Query Language) is an object-oriented query language used to query entities managed by JPA. It resembles SQL, but it refers to Java entity classes, entity properties, and entity relationships instead of directly referencing database tables and columns.

The JPA provider, such as Hibernate, translates JPQL into SQL appropriate for the configured database.

Example of JPQL

SELECT s
FROM Student s
WHERE s.name = :studentName

In this query:

• Student is the JPA entity name.
• s is an alias for the entity.
• s.name refers to the entity's Java property.
• :studentName is a named parameter.

The equivalent SQL depends on the table and column mappings defined in the entity.

4. What Is Native SQL?

A native SQL query is a database SQL statement executed through the JPA or Spring Data JPA infrastructure. It refers directly to physical table names, column names, database functions, and SQL syntax supported by the underlying database.

Example of Native SQL

SELECT *
FROM students
WHERE name = :studentName

Here, students is the database table, and name is the actual database column. These names must match the database schema.

Native SQL is useful when a query needs database-specific functions, specialized SQL syntax, or features that are difficult to express in JPQL.

5. JPQL vs Native SQL

6. Setting Up a Spring Data JPA Project

To use JPQL and native SQL queries in a Spring Boot application, include Spring Data JPA and the JDBC driver for your selected database.

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

Use a compatible Spring Boot version and the JDBC driver that matches your database. The examples below use MySQL-style table and column names.

Database Configuration

spring.datasource.url=jdbc:mysql://localhost:3306/college_db
spring.datasource.username=root
spring.datasource.password=your_password
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

Replace the database name and credentials with your own configuration. For production applications, use secure configuration or environment variables for credentials and choose a controlled schema migration strategy instead of relying on automatic schema updates.

7. Creating a JPA Entity

Before writing queries, define the entity that represents the data your application manages.

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "students")
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String email;
    private Double marks;

    public Student() {
    }

    public Student(String name, String email, Double marks) {
        this.name = name;
        this.email = email;
        this.marks = marks;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Double getMarks() {
        return marks;
    }

    public void setMarks(Double marks) {
        this.marks = marks;
    }
}

The @Entity annotation marks the class as a persistent entity. The @Table annotation maps it to the students table, while @Id identifies its primary key.

JPQL queries will refer to the entity and properties, such as Student and marks. Native SQL queries will refer to the physical table and columns, such as students and marks.

8. Creating the Repository Interface

Spring Data JPA repositories provide a convenient place to declare custom queries. Extend JpaRepository to inherit standard persistence operations.

import org.springframework.data.jpa.repository.JpaRepository;

public interface StudentRepository
        extends JpaRepository<Student, Long> {
}

You can add methods annotated with @Query to define JPQL or native SQL queries directly in this interface.

9. Writing Basic JPQL SELECT Queries

9.1 Retrieve All Students

@Query("SELECT s FROM Student s")
List<Student> findAllStudents();

This query returns all matching student entities. Unlike SQL's SELECT *, JPQL selects an entity using its entity name and alias.

9.2 Retrieve a Student by Name

@Query("SELECT s FROM Student s WHERE s.name = :studentName")
List<Student> findByStudentName(
        @Param("studentName") String studentName);

The named parameter :studentName is linked to the Java method parameter through @Param.

9.3 Find Students with Marks Greater Than a Value

@Query("SELECT s FROM Student s WHERE s.marks > :minimumMarks")
List<Student> findStudentsWithMarksAbove(
        @Param("minimumMarks") Double minimumMarks);

9.4 Retrieve Students in Descending Order of Marks

@Query("SELECT s FROM Student s ORDER BY s.marks DESC")
List<Student> findStudentsOrderByMarksDesc();

JPQL supports filtering, sorting, and selecting entities or individual values. Property names in JPQL must match the entity's Java properties.

10. Using Named and Positional Parameters

Parameters allow values to be supplied at runtime rather than being hard-coded into a query.

Named Parameters

@Query("SELECT s FROM Student s WHERE s.email = :email")
Optional<Student> findStudentByEmail(
        @Param("email") String email);

Named parameters use a readable name preceded by a colon. They are especially useful when queries contain several parameters.

Positional Parameters

@Query("SELECT s FROM Student s WHERE s.marks > ?1 AND s.name = ?2")
List<Student> findByMarksAndName(
        Double marks, String name);

In this example, ?1 represents the first method parameter and ?2 represents the second. Named parameters are often easier to read and maintain.

Important: Never concatenate untrusted user input directly into a query string. Use query parameters to reduce SQL injection risks and ensure values are handled correctly.

11. Using LIKE for Partial Text Searches

The JPQL LIKE operator supports searches based on text patterns. The percent sign (%) represents any sequence of characters, while the underscore (_) represents a single character.

@Query("SELECT s FROM Student s WHERE LOWER(s.name) LIKE LOWER(CONCAT('%', :keyword, '%'))")
List<Student> searchStudentsByName(
        @Param("keyword") String keyword);

This query performs a case-insensitive substring search in many common database configurations. Actual case behavior may depend on database collation and provider behavior.

For example, a keyword of rah can match names such as Rahul or Sarah.

12. Using Aggregate Functions in JPQL

Aggregate functions calculate a result from multiple records. JPQL supports functions such as COUNT, SUM, AVG, MIN, and MAX.

Count Students

@Query("SELECT COUNT(s) FROM Student s")
long countStudents();

Calculate Average Marks

@Query("SELECT AVG(s.marks) FROM Student s")
Double calculateAverageMarks();

Find the Highest Marks

@Query("SELECT MAX(s.marks) FROM Student s")
Double findHighestMarks();

Group Students by Marks

@Query("SELECT s.marks, COUNT(s) FROM Student s GROUP BY s.marks")
List<Object[]> countStudentsByMarks();

When a query selects multiple values, its result may be represented as an array or a projection. For larger applications, consider DTO projections or interface-based projections for clearer result handling.

13. Writing JOIN Queries in JPQL

JPQL can navigate relationships between entities. Suppose a college application contains a Department entity and a Student entity, with each student associated with a department.

Example Using an Entity Relationship

@ManyToOne
@JoinColumn(name = "department_id")
private Department department;

A JPQL query can use this relationship to retrieve students belonging to a particular department.

@Query("SELECT s FROM Student s JOIN s.department d WHERE d.name = :departmentName")
List<Student> findStudentsByDepartment(
        @Param("departmentName") String departmentName);

The expression s.department refers to the mapped Java relationship. The query does not need to specify the physical foreign-key column directly.

LEFT JOIN Example

@Query("SELECT s FROM Student s LEFT JOIN s.department d")
List<Student> findAllStudentsWithOptionalDepartment();

A left join includes students even when their department relationship is absent. To use these examples, create the Department entity and map the relationship correctly.

14. Writing Native SQL Queries with @Query

To execute a native SQL statement using Spring Data JPA, set nativeQuery = true in the @Query annotation.

14.1 Retrieve All Students

@Query(value = "SELECT * FROM students", nativeQuery = true)
List<Student> findAllStudentsNative();

When the selected columns can be mapped to the entity's mapped columns, the query can return a list of Student entities.

14.2 Retrieve a Student by Email

@Query(value = "SELECT * FROM students WHERE email = :email", nativeQuery = true)
Optional<Student> findStudentByEmailNative(
        @Param("email") String email);

14.3 Find Students Above a Marks Threshold

@Query(value = "SELECT * FROM students WHERE marks > :minimumMarks",
       nativeQuery = true)
List<Student> findStudentsAboveMarksNative(
        @Param("minimumMarks") Double minimumMarks);

14.4 Sort Students by Marks

@Query(value = "SELECT * FROM students ORDER BY marks DESC",
       nativeQuery = true)
List<Student> findStudentsSortedByMarksNative();

Native queries use actual database identifiers. If your table or column names differ, update the SQL accordingly. Database-specific syntax may not work unchanged on another database system.

15. Using @NativeQuery in Supported Spring Data JPA Versions

Recent Spring Data JPA versions provide an @NativeQuery annotation as a convenient way to declare native SQL queries. For versions that do not support it, use @Query(nativeQuery = true).

import org.springframework.data.jpa.repository.NativeQuery;

@NativeQuery("SELECT * FROM students WHERE email = :email")
Optional<Student> findStudentByEmailNative(
        @Param("email") String email);

Check the Spring Data JPA version in your project before using this annotation. The @Query(nativeQuery = true) form remains a familiar alternative in many existing projects.

16. JPQL UPDATE Queries

JPQL supports bulk update operations. In Spring Data JPA, pair an update or delete query declared with @Query with the @Modifying annotation.

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

@Modifying
@Transactional
@Query("UPDATE Student s SET s.marks = :marks WHERE s.id = :id")
int updateStudentMarks(
        @Param("id") Long id,
        @Param("marks") Double marks);

The returned integer indicates the number of affected records. Bulk updates operate directly against the database and can leave already-loaded entity instances in the persistence context out of date. Consider clearing or refreshing the persistence context when needed.

17. JPQL DELETE Queries

JPQL can delete records that match a specified condition.

@Modifying
@Transactional
@Query("DELETE FROM Student s WHERE s.id = :id")
int deleteStudentByIdCustom(
        @Param("id") Long id);

This is a bulk delete operation. It does not necessarily behave like deleting each managed entity individually, and entity lifecycle callbacks may not run for bulk-deleted entities.

For ordinary CRUD operations, the inherited deleteById() method from JpaRepository is often sufficient.

18. Native SQL UPDATE and DELETE Queries

Native SQL also supports data modification when the database-specific statement is appropriate for the task.

Native UPDATE

@Modifying
@Transactional
@Query(value = "UPDATE students SET marks = :marks WHERE id = :id",
       nativeQuery = true)
int updateMarksNative(
        @Param("id") Long id,
        @Param("marks") Double marks);

Native DELETE

@Modifying
@Transactional
@Query(value = "DELETE FROM students WHERE id = :id",
       nativeQuery = true)
int deleteStudentNative(
        @Param("id") Long id);

Use @Modifying for repository methods that execute modifying statements through @Query. Transactions can be managed at the repository or service layer; service-level transaction boundaries are often useful when several database operations must succeed or fail together.

19. Returning Single Results and Optional Values

Choose a return type that matches the expected number and type of results.

If a query intended to return one result actually returns multiple records, JPA may throw a non-unique result exception. Use a collection return type when multiple matches are possible.

20. Pagination and Sorting

Pagination helps applications retrieve large datasets in manageable portions. Spring Data JPA supports pagination through Pageable and Page.

JPQL Pagination

@Query("SELECT s FROM Student s WHERE s.marks >= :minimumMarks")
Page<Student> findStudentsByMinimumMarks(
        @Param("minimumMarks") Double minimumMarks,
        Pageable pageable);

Example usage in a service:

Pageable pageable = PageRequest.of(
        0,
        10,
        Sort.by("marks").descending()
);

Page<Student> page =
        studentRepository.findStudentsByMinimumMarks(40.0, pageable);

The page index is zero-based. The example requests the first page containing up to ten students, sorted by marks in descending order.

Native SQL Pagination

@Query(
    value = "SELECT * FROM students WHERE marks >= :minimumMarks",
    countQuery = "SELECT COUNT(*) FROM students WHERE marks >= :minimumMarks",
    nativeQuery = true
)
Page<Student> findStudentsNativePaged(
        @Param("minimumMarks") Double minimumMarks,
        Pageable pageable);

Providing a separate countQuery is useful for native pagination, especially when the query is too complex for automatic count-query derivation. Native query support for dynamic sorting and query rewriting depends on the Spring Data JPA version and query complexity.

21. Using DTOs and Projections

Sometimes an application needs only a few fields rather than a complete entity. Returning a DTO or projection can reduce unnecessary data transfer and make query results easier to use.

JPQL Constructor Expression

package com.example.dto;

public class StudentSummary {
    private final String name;
    private final Double marks;

    public StudentSummary(String name, Double marks) {
        this.name = name;
        this.marks = marks;
    }

    public String getName() {
        return name;
    }

    public Double getMarks() {
        return marks;
    }
}

Declare the query using the fully qualified DTO class name:

@Query("SELECT new com.example.dto.StudentSummary(s.name, s.marks) FROM Student s")
List<StudentSummary> findStudentSummaries();

The constructor parameter types and order must match the selected expressions. Native SQL projections may require aliases, interface projections, or explicit result-set mapping depending on the result structure and JPA provider.

22. Named Queries

Named queries are defined once and referenced by name. They can be useful when a query is stable and should be associated with an entity.

import jakarta.persistence.NamedQuery;

@Entity
@Table(name = "students")
@NamedQuery(
    name = "Student.findByEmail",
    query = "SELECT s FROM Student s WHERE s.email = :email"
)
public class Student {
    // Entity fields, constructors, getters, and setters
}

The repository can declare a method that Spring Data JPA resolves using the entity and method naming convention:

Optional<Student> findByEmail(String email);

For custom query logic, using @Query directly on the repository method is often easier to discover because the query is located beside the method that executes it.

23. Choosing Between JPQL and Native SQL

Use JPQL when the query can be expressed through the entity model and you want better portability across supported databases. Use native SQL when the query requires database-specific capabilities or precise control over the SQL statement.

• Prefer JPQL: standard entity searches, filtering, relationships, aggregate queries, and most application-level CRUD logic.
• Consider native SQL: specialized reporting queries, vendor-specific functions, database features not represented in JPQL, and carefully measured performance cases.
• Prefer derived repository methods: straightforward searches such as findByEmail or findByMarksGreaterThan when the method name remains clear.
• Use a custom repository: when query construction becomes highly dynamic or requires specialized control over the EntityManager.

24. Common Errors and Troubleshooting

25. Security Best Practices

• Use named or positional parameters instead of concatenating user-provided values into JPQL or SQL strings.
• Do not assume parameter binding can safely substitute table names, column names, or SQL keywords; validate dynamic identifiers separately.
• Use authorization checks to ensure users can access only the records they are permitted to see.
• Validate incoming data and apply database constraints where appropriate.
• Use a least-privilege database account for the application.
• Avoid returning sensitive fields when a query needs only a small subset of the data.
• Keep credentials out of source code and version control.

26. Performance Optimization Tips

• Retrieve only the data your application needs, using projections or DTOs where appropriate.
• Use pagination for large result sets instead of loading every record at once.
• Create indexes based on measured query patterns and actual database execution plans.
• Be careful with joins over large relationships and avoid unnecessary eager loading.
• Inspect generated SQL and use database query plans to investigate slow queries.
• Measure performance before choosing native SQL solely because it appears shorter or faster.
• For frequently executed queries, consider query plans, indexing, connection-pool settings, and caching where suitable.
• Review bulk updates and deletes carefully because they may bypass normal entity lifecycle behavior.

27. Practical Exercise: Student Search Application

Create a Spring Boot application that allows users to search for students by name, email, and marks using both JPQL and native SQL.

Requirements

1. Create a Student entity mapped to a students table.
2. Create a StudentRepository extending JpaRepository.
3. Write a JPQL query to find students by name.
4. Write a JPQL query to calculate average marks.
5. Write a native SQL query to find a student by email.
6. Write a JPQL update query to modify student marks.
7. Add pagination for the student search results.
8. Test each repository method using JUnit or an integration test with a test database.

Example Repository

import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface StudentRepository
        extends JpaRepository<Student, Long> {

    @Query("SELECT s FROM Student s WHERE LOWER(s.name) LIKE LOWER(CONCAT('%', :keyword, '%'))")
    List<Student> searchByName(
            @Param("keyword") String keyword);

    @Query("SELECT AVG(s.marks) FROM Student s")
    Double findAverageMarks();

    @Query(value = "SELECT * FROM students WHERE email = :email",
           nativeQuery = true)
    Optional<Student> findByEmailNative(
            @Param("email") String email);
}

This repository combines JPQL for entity-based searching and aggregation with native SQL for a direct database-level lookup.

28. Interview Questions and Answers

Q1. What is JPQL?

JPQL is an object-oriented query language that operates on JPA entities and their properties. The JPA provider translates JPQL into database SQL.

Q2. What is a native SQL query?

A native SQL query is a SQL statement executed directly against the database through JPA or Spring Data JPA.

Q3. What is the main difference between JPQL and SQL?

JPQL refers to entity classes and Java properties, whereas SQL refers to database tables and columns.

Q4. How do you write a custom JPQL query in Spring Data JPA?

Declare a repository method and annotate it with @Query, supplying a JPQL statement.

Q5. How do you execute native SQL with @Query?

Set nativeQuery = true in the @Query annotation and write SQL using actual database identifiers.

Q6. Why are named parameters useful?

They make query definitions more readable and let method parameters be bound to clearly named placeholders.

Q7. What is @Modifying used for?

It tells Spring Data JPA that a declared query performs a modifying operation, such as an update or delete, rather than a normal select.

Q8. Can JPQL perform update and delete operations?

Yes. JPQL supports bulk update and delete statements, generally used through repository methods annotated with @Modifying.

Q9. Can native SQL be database-independent?

Not always. Native queries can rely on vendor-specific syntax, functions, identifiers, and data types, so portability may be limited.

Q10. When should you use a DTO projection?

Use a DTO projection when the application needs selected fields instead of a complete entity, particularly for summary views and reporting.

Q11. How can query injection risks be reduced?

Bind values through named or positional parameters and avoid concatenating untrusted input into query strings.

Q12. How do you paginate a native query?

Use a Pageable parameter and, when needed, supply a matching countQuery so the total number of results can be calculated correctly.

29. Quick Revision Checklist

• JPQL uses entity names and Java properties.
• Native SQL uses physical table and column names.
• @Query is used to declare custom repository queries.
• nativeQuery = true enables native SQL with @Query.
• @Param binds Java method parameters to named query parameters.
• JPQL supports filtering, sorting, joins, grouping, and aggregate functions.
• @Modifying is used for declared update and delete queries.
• @Transactional provides an appropriate transaction boundary for data changes.
• Pagination uses Pageable and may require an explicit count query for native SQL.
• Parameter binding, correct mappings, and query testing are essential for secure and reliable applications.

30. Conclusion

Writing JPQL and native SQL queries is an important skill for building data-driven Java applications with Spring Data JPA. JPQL provides an entity-oriented and generally portable approach, while native SQL gives developers direct access to database-specific syntax and capabilities.

Choose the simplest query mechanism that meets the application's requirements. Use parameter binding, correct entity mappings, transaction management, pagination, and performance testing to keep your persistence layer secure, maintainable, and efficient.

To learn more, explore the Advance Java Training course at JustAcademy, or register for a course demo.

Additional Resource: Spring Data JPA Query Methods Documentation